using System;
using System.Collections.Generic;
using System.Collections.ObjectModel;
using System.Data;
using System.Text;
using System.Data.SQLite;
using System.Linq;
using WordsHelper.DAL.Models.Data;
using WordsHelper.Model.Data;

namespace WordsHelper.DAL.Services.Data
{
    public static class DALDataTableService
    {
        /// <exception cref="ArgumentNullException"></exception>
        /// <exception cref="ArgumentException"></exception>
        public static void SaveToCache(this DALDataTable model)
        {
            if (model == null)
            {
                throw new ArgumentNullException("model");
            }
            if (model.Lists == null)
            {
                throw new ArgumentException("Lists is Null.", "model");
            }
            model.cache = model.Lists.Select(l => l.Clone() as ModelDataTable).ToList();
        }
        /// <exception cref="SQLiteException"></exception>
        /// <exception cref="ArgumentNullException"></exception>
        /// <exception cref="ArgumentException"></exception>
        public static void SaveChanges(this DALDataTable model)
        {
            if (model == null)
            {
                throw new ArgumentNullException("model");
            }
            if (model.Lists == null || model.cache == null)
            {
                throw new ArgumentException("Lists and cache is Null.", "model");
            }
            SaveChanges(model.cache, model.Lists);
        }
        /// <exception cref="ArgumentNullException"></exception>
        /// <exception cref="ArgumentException"></exception>
        public static void DropChanges(this DALDataTable model)
        {
            if (model == null)
            {
                throw new ArgumentNullException("model");
            }
            if (model.cache == null)
            {
                throw new ArgumentException("cache is Null.", "model");
            }
            model.Lists = model.cache.Select(l => l.Clone() as ModelDataTable).ToList();
        }

        /// <summary>
        /// 获得符合指定条件的数据列表
        /// </summary>
        /// <param name="strWhere">指定的条件，不需要包括“where”，当为null或string.Empty时表中所有数据列表</param>
        /// <returns></returns>
        /// <exception cref="SQLiteException"></exception>
        internal static List<ModelDataTable> GetModelList(string strWhere)
        {
            return DataTableToList(GetList(strWhere).Tables[0]);
        }
		/// <summary>
        /// 获得符合指定条件的分页数据列表
        /// </summary>
        /// <param name="strWhere">指定的条件，不需要包括“where”，当为null或string.Empty时表中所有数据列表</param>
        /// <param name="strOrderby">排序字段，不需要包括“order by”,当为null或string.empty是为默认排序</param>
        /// <param name="limit">所取的记录数</param>
        /// <param name="offset">跳过的记录数</param>
        /// <returns></returns>
        /// <exception cref="SQLiteException"></exception>
        /// <exception cref="ArgumentException"></exception>
		internal static List<ModelDataTable> GetModelList(string strWhere, string strOrderby,
            int limit, int offset)
        {
            if (limit < 0)
            {
                throw new ArgumentException("Is Negative.", "limit");
            }
            if (offset < 0)
            {
                throw new ArgumentException("Is Negative.", "offset");
            }
            return DataTableToList(GetList(strWhere, strOrderby, limit, offset).Tables[0]);
        }
        /// <summary>
        /// 比较并更新数据，对于增加了的数据，插入数据库；对于删除了的数据，从数据库中删除；对于修改了的数据，更新至数据库中。
        /// </summary>
        /// <param name="oldModels">旧数据列表，刚从数据查找出来的数据列表</param>
        /// <param name="newModels">新数据列表，结果程序增加、删除或修改之后的数据列表</param>
        /// <returns></returns>
        /// <exception cref="SQLiteException"></exception>
        /// <exception cref="ArgumentNullException"></exception>
        internal static bool SaveChanges(List<ModelDataTable> oldModels, List<ModelDataTable> newModels)
        {
            if (oldModels == null)
            {
                throw new ArgumentNullException("oldModels");
            }
			if (newModels == null)
            {
                throw new ArgumentNullException("newModels");
            }
            if (!oldModels.Any() && !newModels.Any())
            {
                return true;
            }
            var addedModels = newModels.Where(m => m.ID == 0).ToList();
            var oldModelIds = oldModels.Select(m => m.ID).ToList();
            var newModelIds = newModels.Select(m => m.ID);
            var deletedIds = oldModelIds.Where(i => !newModelIds.Contains(i)).ToList();
            var b = newModels.Select(model => new { model, index = oldModelIds.IndexOf(model.ID) });
            var updatedModels = (b.Where(m => m.index >= 0)
                                          .Where(m => oldModels[m.index].NotEquals(m.model))
                                          .Select(m => m.model)).ToList();
            var addedIDs = AddList(addedModels);
            for (var i = 0; i < addedModels.Count && i < addedIDs.Count; i++)
            {
                addedModels[i].ID = addedIDs[i];
            }
            var ids = deletedIds.Aggregate(string.Empty, (s, d) => s + "," + d).Trim(',');
            return (ids.Length == 0 || DeleteList(ids)) 
                && (updatedModels.Count == 0 || UpdateList(updatedModels));
        }

        /// <summary>
        /// 增加一条数据
        /// </summary>
        /// <param name="model">不需要指定ID</param>
        /// <returns>插入的数据的ID</returns>
        /// <exception cref="SQLiteException"></exception>
        /// <exception cref="ArgumentNullException"></exception>
        private static List<int> AddList(List<ModelDataTable> model)
        {
            if (model == null)
            {
                throw new ArgumentNullException("model");
            }
            if (!model.Any())
            {
                return new List<int>();
            }
            var strSqlList = new Collection<string>();
            var parametersList = new Collection<SQLiteParameter[]>();
            foreach (var tableModel in model)
            {
                var strSql = new StringBuilder();
                strSql.Append("insert into DataTable(");
                                strSql.Append("Name,Meaning,Important)");
                strSql.Append(" values (");
                                strSql.Append("@Name,@Meaning,@Important)");
                strSql.Append(";select LAST_INSERT_ROWID()");
                var parameters = new[]
                    {
                            new SQLiteParameter("@Name", DbType.String),
                            new SQLiteParameter("@Meaning", DbType.String),
                            new SQLiteParameter("@Important", DbType.Int32)                
                    };
                parameters[0].Value = tableModel.Name;
                parameters[1].Value = tableModel.Meaning;
                parameters[2].Value = tableModel.Important;
                
                strSqlList.Add(strSql.ToString());
                parametersList.Add(parameters);
            }

            var obj = DbHelperSQLite.GetSingle(DataSourceRepository.DataDataSource.DataSource, strSqlList, parametersList);
            return obj.Select(o => o == null ? 0 : Convert.ToInt32(o)).ToList();
        }

        /// <summary>
        /// 删除多条数据
        /// </summary>
        /// <param name="idList">待删除的数据的ID的列表，以“,”分割</param>
        /// <returns>true表示删除成功</returns>
        /// <exception cref="SQLiteException"></exception>
        /// <exception cref="ArgumentNullException"></exception>
        private static bool DeleteList(string idList)
        {
            if (idList == null)
            {
                throw new ArgumentNullException("idList");
            }
            if (idList.Length == 0)
            {
                return true;
            }
            var strSql = new StringBuilder();
            strSql.Append("delete from DataTable ");
            strSql.Append(" where ID in (" + idList + ")  ");
            return DbHelperSQLite.ExecuteSql(DataSourceRepository.DataDataSource.DataSource, strSql.ToString()) > 0;
        }
        /// <summary>
        /// 更新多条数据
        /// </summary>
        /// <param name="modelList">待更新的数据需要指定ID</param>
        /// <returns>true表示更新成功</returns>
        /// <exception cref="SQLiteException"></exception>
        /// <exception cref="ArgumentNullException"></exception>
        private static bool UpdateList(List<ModelDataTable> modelList)
        {
            if (modelList == null)
            {
                throw new ArgumentNullException("modelList");
            }
            if (!modelList.Any())
            {
                return true;
            }
            var strSqlList = new Collection<string>();
            var parametersList = new Collection<SQLiteParameter[]>();
            foreach (var model in modelList)
            {
                var strSql = new StringBuilder();
                strSql.Append("update DataTable set ");
                strSql.Append("Name=@Name,");
                strSql.Append("Meaning=@Meaning,");
                strSql.Append("Important=@Important");
                            
                strSql.Append(" where ID=@ID");
                var parameters = new[]
                    {
                        new SQLiteParameter("@ID", DbType.Int32),
                        new SQLiteParameter("@Name", DbType.String),
                        new SQLiteParameter("@Meaning", DbType.String),
                        new SQLiteParameter("@Important", DbType.Int32)
                    };
                parameters[0].Value = model.ID;
                parameters[1].Value = model.Name;
                parameters[2].Value = model.Meaning;
                parameters[3].Value = model.Important;
                
                strSqlList.Add(strSql.ToString());
                parametersList.Add(parameters);
            }
            return DbHelperSQLite.ExecuteSqlList(DataSourceRepository.DataDataSource.DataSource, strSqlList, parametersList) > 0;
        }
        /// <param name="strWhere"></param>
        /// <returns></returns>
        /// <exception cref="SQLiteException"></exception>
        private static DataSet GetList(string strWhere)
        {
            var strSql = new StringBuilder();
            strSql.Append("select * ");
            strSql.Append(" FROM DataTable ");
            if (strWhere != null && strWhere.Trim().Length != 0)
            {
                strSql.Append(" where " + strWhere);
            }
            return DbHelperSQLite.Query(DataSourceRepository.DataDataSource.DataSource, strSql.ToString());
        }
        /// <exception cref="SQLiteException"></exception>
        /// <exception cref="ArgumentException"></exception>
		private static DataSet GetList(string strWhere, string strOrderby, int limit, int offset)
        {
            if (limit < 0)
            {
                throw new ArgumentException("Is Negative.", "limit");
            }
            if (offset < 0)
            {
                throw new ArgumentException("Is Negative.", "offset");
            }
            var strSql = new StringBuilder();
            strSql.Append("select * ");
            strSql.Append(" FROM DataTable ");
            if (strWhere != null && strWhere.Trim().Length != 0)
            {
                strSql.Append(" where " + strWhere);
            }
            if (strOrderby != null && strOrderby.Trim().Length != 0)
            {
                strSql.Append(" order by " + strOrderby);
            }
            strSql.Append(" limit " + limit);
            strSql.Append(" offset " + offset + ";");
            return DbHelperSQLite.Query(DataSourceRepository.DataDataSource.DataSource, strSql.ToString());
        }
        /// <exception cref="ArgumentNullException"></exception>
        /// <exception cref="ArgumentException"></exception>
        private static List<ModelDataTable> DataTableToList(DataTable dt)
        {
            if (dt == null)
            {
                throw new ArgumentNullException("dt");
            }
            if (dt.Rows == null)
            {
                throw new ArgumentException("Rows are Null.", "dt");
            }
            var modelList = new List<ModelDataTable>();
            for (var n = 0; n < dt.Rows.Count; n++)
            {
                var model = DataRowToModel(dt.Rows[n]);
                modelList.Add(model);
            }
            return modelList;
        }
        /// <exception cref="ArgumentNullException"></exception>
        private static ModelDataTable DataRowToModel(DataRow row)
        {
            if (row == null)
            {
                throw new ArgumentNullException("row");
            }
            var model = new ModelDataTable();
            if (row["ID"] != null && row["ID"].ToString().Length != 0)
            {
                model.ID = Int32.Parse(row["ID"].ToString());
            }
            if (row["Name"] != null)
            {
                model.Name = row["Name"].ToString();
            }
            if (row["Meaning"] != null)
            {
                model.Meaning = row["Meaning"].ToString();
            }
            if (row["Important"] != null && row["Important"].ToString().Length != 0)
            {
                model.Important = Int32.Parse(row["Important"].ToString());
            }
            return model;
        }
        private static bool NotEquals(this ModelDataTable model1,
                                       ModelDataTable model2)
        {
            if (model1 == null && model2 == null)
            {
                return true;
            }
            if (model1 == null || model2 == null)
            {
                return false;
            }
            return model1.ID != model2.ID || model1.Name != model2.Name || model1.Meaning != model2.Meaning || model1.Important != model2.Important;
        }
    }
}